# -*- coding: utf-8 -*-

"""
@Datetime: 2019/4/14
@Author: Zhang Yafei
"""
# import xlrd
#
# myWorkbook = xlrd.open_workbook('data.xlsx')
#
# # 读取excel的三种方式
#
# # 方式一
# mySheets = myWorkbook.sheets()  # 获取工作表list。
# mySheet = mySheets[0]  # 通过索引顺序获取。
# # 方式二
# # mySheet1 = myWorkbook.sheet_by_index(0)  # 通过索引顺序获取。
# # 方式三
# # mySheet2 = myWorkbook.sheet_by_name('table')  # 通过名称获取。
#
# # 获取行数和列数
# nrows = mySheet.nrows
# ncols = mySheet.ncols
# print(nrows, ncols)
#
# # 获取一行和一列
# myRowValues = mySheet.row_values(0)  # i是行数，从0开始计数，返回list对象。
#
# myColValues = mySheet.col_values(0)  # i是列数，从0开始计数，返回list对象。
#
# print(myRowValues, myColValues)
#
# # 读取单元格数据
# # myCell = mySheet.cell(0, 0)  # 获取单元格，i是行数，j是列数，行数和列数都是从0开始计数。
# # myCellValue = myCell.value  # 通过单元格获取单元格数据。
# myCellValue = mySheet.cell_value(0, 0)  # 直接获取单元格数据，i是行数，j是列数，行数和列数都是从0开始计数。
# print(myCellValue)

# # 写入
# import xlwt
#
# # 创建Excel工作薄
# myWorkbook = xlwt.Workbook()
#
# # 添加Excel工作表
# mySheet = myWorkbook.add_sheet('A Test Sheet')
#
# # 写入数据
# myStyle = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00')   #数据格式
# mySheet.write(1, 1, 1234.56, myStyle)
# mySheet.write(2, 0, 1)                          #写入A3，数值等于1
# mySheet.write(2, 1, 1)                          #写入B3，数值等于1
# mySheet.write(2, 2, xlwt.Formula("A3+B3"))      #写入C3，数值等于2（A3+B3）
#
# # 保存
# myWorkbook.save('excelFile.xlsx')

# xlutils结合xlrd可以达到修改excel文件目的
# import xlrd
# from xlutils.copy import copy
#
# workbook = xlrd.open_workbook('excelFile.xlsx')
# workbooknew = copy(workbook)
# ws = workbooknew.get_sheet(0)
# ws.write(3, 0, 'changed!')
# workbooknew.save('excelFilecopy.xls')

# openpyxl可以对excel文件进行读写操作
# from openpyxl import Workbook
# from openpyxl import load_workbook
# from openpyxl.writer.excel import ExcelWriter
#
# workbook_ = load_workbook("data.xlsx")
# sheetnames = workbook_.get_sheet_names()  # 获得表单名字
# print(sheetnames)
# sheet = workbook_.get_sheet_by_name(sheetnames[0])
# print(sheet.cell(row=3, column=3).value)
# sheet['A1'] = '47'
# workbook_.save("data_new.xlsx")
# wb = Workbook()
# ws = wb.active
# ws['A1'] = 4
# wb.save("data_new2.xlsx")

# xlsxwriter可以写excel文件并加上图表

import xlsxwriter


def get_chart(series):
    chart = workbook.add_chart({'type': 'line'})
    for ses in series:
        name = ses["name"]
        values = ses["values"]
        chart.add_series({
            'name': name,
            'categories': 'A2:A10',
            'values': values
        })
    chart.set_size({'width': 700, 'height': 350})
    return chart


if __name__ == '__main__':
    workbook = xlsxwriter.Workbook('H5应用中心关键数据及趋势.xlsx')
    worksheet = workbook.add_worksheet("每日PV,UV")
    headings = ['日期', '平均值']
    worksheet.write_row('A1', headings)
    index = 0
    for row in range(1, 10):
        for com in [0, 1]:
            worksheet.write(row, com, index)
            index += 1
    series = [{"name": "平均值", "values": "B2:B10"}]
    chart = get_chart(series)
    chart.set_title({'name': '每日页面分享数据'})
    worksheet.insert_chart('H7', chart)
    workbook.close()
